package com.scalar.chiptrack.operations.dataentry.dao;

import com.scalar.ScalarException;
import com.scalar.chiptrack.db.DataSource;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.operations.dataentry.InTransitLots;
import com.scalar.chiptrack.utils.StringUtils;
import com.scalar.chiptrack.utils.SqlQueryMappings;
import com.scalar.chiptrack.adaptor.AdaptorConstants;
import com.scalar.chiptrack.users.User;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.users.dao.NotificationSetupDAO;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.Calendar;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.CallableStatement;

import org.apache.struts.util.LabelValueBean;

/**
 * Created by Sudhakar.
 * Date: Jun 14, 2005
 * Time: 12:12:19 PM
 */
public class FGIShipLotsDAO implements AdaptorConstants
{

    /**
     *
     * @return
     * @throws com.scalar.ScalarException
     */
    public static ArrayList loadAllTransitLots( String processType, User user, int userType ) throws ScalarException
    {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ArrayList fgShipLotsList = new ArrayList();
        ResultSet rset = null;

        try
        {
            conn = DataSource.getConnection();
            //User type is 0 for External User, Getting the lots based on the External User Vendor Id
            if( userType == 0 )
            {
                pstmt = conn.prepareStatement( "SELECT * FROM FN_LOAD_ALL_TRANSIT_LOTS(0) FN, ASSM_RELEASE_TABLE ART WHERE PROCESS_TYPE_NAME IN (?, 'Class Store')  AND ART.ASSEMBLY_RELEASE_NO = FN.LOT_NUMBER  AND ART.SHIP_TO_VENDOR_ID = ? ORDER BY FN.PS_TYPE_ID" );
                pstmt.setString( 1, processType );
                pstmt.setInt ( 2, user.getVendorId() );
            }
            else
            {
                pstmt = conn.prepareStatement( "SELECT * FROM FN_LOAD_ALL_TRANSIT_LOTS(0) WHERE PROCESS_TYPE_NAME IN (?)  ORDER BY PS_TYPE_ID" );
                pstmt.setString( 1, processType );
            }
            rset = pstmt.executeQuery();
            InTransitLots inTransitLots = null;

            while ( rset.next() )
            {
                inTransitLots = new InTransitLots();
                
                inTransitLots.setProcessStepId( rset.getString( "PROCESSSTEP_ID" ) );
                inTransitLots.setPartNumber( rset.getString( "Part_Number" ) );
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setFabLotNumber( rset.getString( "Fab_Lot_Number" ) );
                inTransitLots.setShipDate( rset.getString( "Ship_Date" ) );
                inTransitLots.setShipQty( rset.getString( "Ship_Qty" ) );
                inTransitLots.setGrossDie( rset.getInt( "Gross_Die" ) );
                inTransitLots.setGrossDiePerWafer( rset.getInt( "GDPW" ) );
                inTransitLots.setShippedLotID( rset.getString( "ShippedLot_Id" ) );
                inTransitLots.setVendorName( rset.getString( "Previous_Loc") );
                inTransitLots.setNextLocation( rset.getString( "NEXT_LOC") ) ;
                inTransitLots.setWaferId( rset.getString("Wafer_ID") );
                inTransitLots.setShipReqId( rset.getInt("Ship_Req_id") );
                inTransitLots.setLotId( rset.getInt("Lot_id") );
                inTransitLots.setVendorCode( rset.getString("VENDOR_CODE") );
                inTransitLots.setDieBankLotNo( rset.getString("DB_LOTNO") );
                inTransitLots.setDieBankprocessStepId( rset.getInt("DB_PROCESSSTEP_ID") );
                
                fgShipLotsList.add( inTransitLots );
            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all FGShip Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return fgShipLotsList;
    }

    /**
     *
     * @param processType
     * @param lotNumber
     * @return
     * @throws ScalarException
     */
    public static InTransitLots loadAllTSMCTransitLots(String processType, String lotNumber ) throws ScalarException
    {
        Connection conn             = null;
        PreparedStatement pstmt     = null;
        ResultSet rset              = null;
        InTransitLots inTransitLots = null;

        try
        {
            conn = DataSource.getConnection();
            pstmt = conn.prepareStatement( "SELECT * FROM FN_LOAD_TSMC_TRANSIT_LOTS(?,?) " );
            pstmt.setString( 1, processType );
            pstmt.setString( 2, lotNumber );
            rset = pstmt.executeQuery();


            if ( rset.next() )
            {
                inTransitLots = new InTransitLots();
                inTransitLots.setPartNumber( rset.getString( "Part_Number" ) );
                inTransitLots.setLotNumber( rset.getString( "Lot_Number" ) );
                inTransitLots.setWaferId( rset.getString("Wafer_ID") );
                inTransitLots.setNextLocation( rset.getString("NEXT_LOC"));

            }
        }
        catch ( Exception sqle )
        {
            sqle.printStackTrace();
            throw new ScalarException( "Could not load all TSMC Ship Lots from the database", sqle );
        }
        finally
        {
            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( pstmt != null )
            {
                try
                {
                    pstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
        return inTransitLots;
    }

    /**
     *
     * @param dbTransaction
     * @param fgilotsIterator
     * @throws ScalarException
     */
    public static void modifyShipQty( DBTransaction dbTransaction, Iterator fgilotsIterator ) throws ScalarException
    {
        Connection conn = null;
        CallableStatement cstmt = null;
        ResultSet rset = null;
        boolean success = false;
        InTransitLots inTransitLots = null;
        int quantity = 0;

        try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }

            Calendar cal = Calendar.getInstance();
            java.util.Date todayDate = cal.getTime();
            String currentDate = StringUtils.formatDateToString( todayDate, "MM/dd/yyyy" );
            conn = DataSource.getConnection();
            cstmt = conn.prepareCall(  "{call sp_create_update_fgi_lot(?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) }" );
            while ( fgilotsIterator.hasNext() )
            {
                inTransitLots = ( InTransitLots ) fgilotsIterator.next();
                try
                {
                    quantity = Integer.parseInt(inTransitLots.getRecivedShipQty());
                }
                catch(Exception npe)
                {
                    quantity = 0;
                }
                if( quantity == 0)
                {
                    try
                    {
                        quantity = Integer.parseInt( inTransitLots.getShipQty() );
                    }
                    catch(Exception e )
                    {
                        quantity = 0;
                    }
                }
                cstmt.setString( 1, inTransitLots.getPartNumber() );
                cstmt.setString( 2, inTransitLots.getLotNumber() );
                cstmt.setInt   ( 3, quantity );
                cstmt.setString( 4, currentDate );
                cstmt.setString( 5, inTransitLots.getNextLocation() );
                cstmt.setString( 6, inTransitLots.getComments() );
                cstmt.setString( 7, LOT_STATUS_RUNNING );
                cstmt.setString( 8, inTransitLots.getShippedLotID() );
                cstmt.setInt( 9, inTransitLots.getShipReqId() );
                cstmt.setInt( 10, inTransitLots.getLotId() );
                cstmt.addBatch();
            }

            cstmt.executeBatch();

            success = true;
        }
        catch ( Exception sqle )
        {
            System.out.println( "sqle" + sqle );
            sqle.printStackTrace();
            throw new ScalarException( "Could not Close FGI Lots from the database", sqle );
        }
        finally
        {
            try
            {
                if ( dbTransaction == null )
                {
                    if ( conn != null )
                    {
                        if ( success == true )
                        {
                            conn.commit();
                        }
                        else
                        {
                            conn.rollback();
                        }
                    }
                }
                else
                {
                    if ( success == true )
                    {
                        dbTransaction.commit();
                    }
                    else
                    {
                        dbTransaction.rollback();
                    }
                }
            }
            catch ( SQLException ignore )
            {
            }

            if ( rset != null )
            {
                try
                {
                    rset.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( cstmt != null )
            {
                try
                {
                    cstmt.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
            if ( conn != null )
            {
                try
                {
                    conn.close();
                }
                catch ( SQLException ignore )
                {
                }
            }
        }
    }
    /** To get the location names having the process type as Class Store
     *
     * @return ArrayList
     * @throws com.scalar.ScalarException
     */
    public static ArrayList loadClassStroeLocations( DBTransaction dbTransaction, User user, int userType ) throws ScalarException
    {
        Connection        conn      = null;
	    PreparedStatement pstmt     = null;
	    ResultSet         rs        = null;
        ArrayList         locations = null;

	    try
        {
            if ( dbTransaction == null )
            {
                conn = DataSource.getConnection();
            }
            else
            {
                conn = dbTransaction.getConnection();
            }
            //User Type 0 means External User, Loading only the external User Vendor.
            if( userType == 0 )
            {
                pstmt = conn.prepareStatement ( "SELECT L_VENDOR_NAME AS LOCATION_NAME, LOCATION_ID FROM LOCATION WHERE L_TYPE LIKE 'CLASS STORE' AND L_IS_DELETED = 'N' AND VENDOR_ID = ? ORDER BY  L_VENDOR_NAME" );
                pstmt.setInt( 1, user.getVendorId() );
            }
            else
            {
                pstmt = conn.prepareStatement ( SqlQueryMappings.get("operations.process.vendorLocation.ClassStoreLocations") );
            }
            rs = pstmt.executeQuery();

            locations = new ArrayList();

            while ( rs.next() )
            {
                locations.add( new LabelValueBean( rs.getString("LOCATION_NAME"), rs.getInt("LOCATION_ID")+"" ) );
            }
        }
        catch(SQLException sql)
        {
			sql.printStackTrace();
			throw new ScalarException("Could not load Class Store locations", sql);
		}
        finally
        {
            if(rs != null)
            {
            	try{ rs.close();}catch(Exception e){}
            }
			if(pstmt != null)
            {
				try{ pstmt.close(); }catch(Exception e){}
			}
			if(conn != null)
            {
				try{ conn.close(); }catch(Exception e){}
			}
		}
        return locations;
    }

    /** To send an alert, when quantity pulled from Fgi Reciepts
     *
     * @param blankFgiLotsList
     * @return
     * @throws Exception
     */
    public static boolean notifyUserBlankFgiLots( ArrayList blankFgiLotsList ) throws Exception
    {

        InTransitLots inTransitLots = null;
        boolean success = false;


        String[] emailArray = null;
        try
        {
            emailArray = NotificationSetupDAO.loadEmailsByType( NOTIFICATION_EMAIL_TYPE_LOT_FLOW );
        }
        catch (ScalarException se)
        {
            se.getRootCause().printStackTrace();
            System.out.println( "Exception while retrieving email list" );
        }

        String from = ConfigManager.getScalarAdminEmail();
        int index = from.lastIndexOf( "\\" );
        from = from.substring( index + 1, from.length() );
        String to = StringUtils.concatStrings( emailArray, Mailer.EMAIL_SEPARATOR );
        String subject = "Quantity Pulled From FGI Receipts";

        // FORMAT MESSAGE
        StringBuffer buf = new StringBuffer( 2098 );
        buf.append( "<HTML><HEAD></HEAD><BODY>" );
        buf.append( "<TABLE ALIGN = 'CENTER' style='FONT-SIZE: 8pt; COLOR: black; FONT-FAMILY: Verdana' borderColor='#ffffff' cellSpacing='1' cellPadding='3' width='50%' border=0>" );
        buf.append( "<TR bgColor='#ccccff'><TD colspan='6' width='100%' align='center' ><B>Quantity Pulled From FGI Receipts<B></TD>" );
        buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'><B>SL NO.<B></TD><TD width='8%' align='CENTER'><B>Lot Number<B></TD><TD width='8%' align='CENTER'><B>Part Number<B></TD><TD width='8%' align='CENTER'><B>Total Qty<B></TD><TD width='8%' align='CENTER'><B>Received Qty<B></TD><TD width='8%' align='CENTER'><B>Received By<B></TD></TR>" );
        for ( int i = 0; i < blankFgiLotsList.size(); i++ )
        {
            inTransitLots = ( InTransitLots ) blankFgiLotsList.get( i );
            buf.append( "<TR bgColor='#e6e6e6'><TD width='2%' align='CENTER'>" ).append( ( i + 1 ) + "." );
            buf.append( "</TD><TD width='4%' align='CENTER'>" + inTransitLots.getLotNumber() + "</TD>" );
            buf.append( "<TD width='4%' align='CENTER'>" + inTransitLots.getPartNumber() + "</TD>" );
            buf.append( "<TD width='4%' align='CENTER'>" + inTransitLots.getShipQty() + "</TD>" );
            buf.append( "<TD width='4%' align='CENTER'>" + inTransitLots.getRecivedShipQty() + "</TD>" );
            buf.append( "<TD width='4%' align='CENTER'>" + inTransitLots.getNextLocation() + "</TD></TR>" );
        }
        buf.append( "</TR></TABLE><BODY></HTML>\n" );
        String message = buf.toString();

        try
        {
            // SEND MESSAGE
            Mailer.send( from, to, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
            success = true;
        }
        catch (ScalarException e)
        {
            throw new ScalarException( "Exception while sending new Lot mail notifaction" );
        }
        return success;
    }


}
